package com.ruoyi.mapper;

import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.Date;
import java.util.List;
import com.ruoyi.domain.Book;
import com.ruoyi.pojo.vo.BookAuthorVo;
import com.ruoyi.pojo.vo.BookLanguageVo;
import com.ruoyi.pojo.vo.BookPublishVo;
import com.ruoyi.pojo.vo.UserVO;
import org.apache.ibatis.annotations.*;

/**
 * 图书管理Mapper接口
 *
 * @author qyb
 * @date 2023-12-26
 */
@Mapper
public interface BookMapper

{
    @Select("select author ,author_value from sys_book_author_dict")
    List<BookAuthorVo> selectBookAuthor();
    @Select("select author ,author_value from sys_book_author_dict WHERE (author LIKE CONCAT ('%', #{author}, '%')) ")
    List<BookAuthorVo> selectBookAuthorMOhu(String author);
    @Select("select language ,language_value from sys_book_language_dict WHERE (language LIKE CONCAT ('%', #{language}, '%')) ")
    List<BookLanguageVo> selectBookLanguageMOhu(String language);
    @Select("select publish ,publish_value from sys_book_publish_dict")
    List<BookPublishVo> selectBookPublish();
    @Select("select publish ,publish_value from sys_book_publish_dict WHERE (publish LIKE CONCAT ('%', #{publish}, '%'))")
    List<BookPublishVo> selectBookPublishMOhu(String publish);
    @Select("select language ,language_value from sys_book_language_dict")
    List<BookLanguageVo> selectBookLanguage();

    @Select("select user_id , user_name from sys_user")
    List<UserVO> selectUserVo();
    @Select("select author from sys_book_author_dict where author_value = #{value}")
    String author(int value);
    @Select("select publish from sys_book_publish_dict where publish_value = #{value}")
    String publish(int value);
    @Select("select language from sys_book_language_dict where language_value = #{value}")
    String language(int value);
    @Select("select author from sys_book_author_dict where value = #{value}")
    String category(int value);

//    @Insert("insert into sys_book_author_dict (author,author_value,create_time) values (#{author},#{size},#{createTime})")
//    int addBookAuthor (String author,int size, Date createTime);
    @Insert("insert into sys_book_author_dict (author, author_value, create_time) values (#{param1}, #{param2}, #{param3})")
    int addBookAuthor(String author, int size, Date createTime);
//    @Insert("insert into sys_book_publish_dict (publish,create_time) values (#{publish},#{createTime})")
//    int addBookPublish (String publish,int size,Date createTime);
    @Insert("insert into sys_book_publish_dict (publish, publish_value, create_time) values (#{param1}, #{param2}, #{param3})")
    int addBookPublish(String publish, int size, Date createTime);
//    @Insert("insert into sys_book_language_dict (language,create_time) values (#{language},#{createTime})")
//    int addBookLanguage (String language,Date createTime);
@Insert("insert into sys_book_language_dict (language, language_value, create_time) values (#{param1}, #{param2}, #{param3})")
int addBookLanguage(String language, int size, Date createTime);

    @Insert("INSERT INTO sys_book (name, author, publish, introduction, price, language, number, url, category_id, location, isbn, create_by,create_time) " +
            "VALUES (#{name}, #{author}, #{publish}, #{introduction}, #{price}, #{language}, #{number}, #{url}, #{categoryId}, #{location}, #{isbn},#{createBy},#{createTime})")
    int insert1Book(Book book);

    @Update("update sys_book set is_delete=1 where book_id =#{bookId}")
    int deleteBook(@Param("bookId") Long bookId);

    @Update("UPDATE sys_book " +
            "SET name = #{name}, " +
            "    author = #{author}, " +
            "    publish = #{publish}, " +
            "    introduction = #{introduction}, " +
            "    price = #{price}, " +
            "    language = #{language}, " +
            "    number = #{number}, " +
            "    url = #{url}, " +
            "    category_id = #{categoryId}, " +
            "    location = #{location}, " +
            "    isbn = #{isbn}, " +
            "    update_by = #{updateBy}, " +
            "    update_time = #{updateTime}, " +
            "    is_delete = #{isDelete} " +
            "WHERE book_id = #{bookId}")
    int updateBook(Book book);


    // 使用分页查询，注意这里没有写分页的 SQL，PageUtils 会在查询前自动设置分页信息
    @Select("SELECT * FROM sys_book  WHERE (name LIKE CONCAT ('%', #{name}, '%') OR isbn = #{name}) AND is_delete = 0")
    List<Book> selectAllBooksPaged(@Param("name") String searchByName);
    @Select("SELECT * FROM sys_book WHERE (name LIKE CONCAT ('%', #{name}, '%') OR isbn = #{name}) AND is_delete = 0 LIMIT #{pageSize} OFFSET #{offset}")
    List<Book> selectAllBooksPaged1(@Param("name") String searchByName, @Param("pageSize") int pageSize, @Param("offset")int offset);
//    @Select("SELECT * FROM sys_book LIMIT = #{pageSize} OFFSET = #{pageNum}")
//    List<Book> selectAllBooksPaged2(@Param("pageSize") int pageSize, @Param("offset")int pageNum);


//    @Select("SELECT * FROM sys_book  WHERE (name LIKE CONCAT ('%', #{name}, '%') OR author LIKE CONCAT ('%', #{name}, '%') OR  publish LIKE CONCAT ('%', #{name}, '%') OR isbn = #{name})  AND is_delete = 0")
//    List<Book> selectAllBooksPaged(@Param("name") String searchByName);
    /**
     * 查询图书管理
     *
     * @param bookId 图书管理主键
     * @return 图书管理
     */
    public Book selectBookByBookId(Long bookId);



    /**
     * 查询所有的作者
     * @return
     */
    @Select("SELECT DISTINCT\n" +
            "\tsys_book_author_dict.author \n" +
            "FROM\n" +
            "\tsys_book_author_dict,sys_book \n" +
            "WHERE\n" +
            "\tsys_book.author = sys_book_author_dict.author_value")
    List<String> getAllAuthor();

    /**
     * 查询该作者对应的书的数量
     * @return
     */
    @Select("SELECT\n" +
            "\tsum( number ) \n" +
            "FROM\n" +
            "\tsys_book \n" +
            "WHERE\n" +
            "\tauthor = (\n" +
            "\tSELECT DISTINCT\n" +
            "\t\tauthor_value \n" +
            "\tFROM\n" +
            "\t\tsys_book,\n" +
            "\t\tsys_book_author_dict \n" +
            "\tWHERE\n" +
            "\tsys_book_author_dict.author = #{auth})")
    Integer getAuthorBooks(String auth);


    /**
     * 查询所有的语言
     * @return
     */
    @Select("SELECT DISTINCT\n" +
            "\tsys_book_language_dict.`language` \n" +
            "FROM\n" +
            "\tsys_book_language_dict,sys_book \n" +
            "WHERE\n" +
            "\tsys_book.`language` = sys_book_language_dict.language_value")
    List<String> getAllLanguage();

    /**
     * 查询每种语言对应的书的数量
     * @param language
     * @return
     */
    @Select("SELECT\n" +
            "\tsum( number ) \n" +
            "FROM\n" +
            "\tsys_book \n" +
            "WHERE\n" +
            "\t`language` = (\n" +
            "\tSELECT DISTINCT\n" +
            "\t\tlanguage_value \n" +
            "\tFROM\n" +
            "\t\tsys_book,\n" +
            "\t\tsys_book_language_dict \n" +
            "\tWHERE\n" +
            "\tsys_book_language_dict.`language` = #{language}" +
            "\t)")
    Integer getLanguageNum(String language);


    public int changeBookNum(Book book);

    @Select("SELECT day from sys_day_book_num")
    List<LocalDate> getDay();


    @Select("select number from sys_day_book_num where day = #{date}")
    Integer getDayBookNum(LocalDate date);

}
